/**
 * MySQL数据库连接模块
 */
const mysql = require('mysql2/promise');
const logger = require('./logger');

// 数据库连接池
let pool = null;

// 创建数据库连接池
async function createPool() {
    if (pool) return pool;
    
    pool = mysql.createPool({
        host: process.env.DB_HOST || '182.160.6.140',
        user: process.env.DB_USER || 'root',
        password: process.env.DB_PASSWORD || 'BinanceRoot2023',
        database: process.env.DB_NAME || 'binance_klines',
        port: process.env.DB_PORT || 3306,
        waitForConnections: true,
        connectionLimit: 10,
        queueLimit: 0
    });
    
    logger.info('数据库连接池已创建');
    return pool;
}

// 执行查询
async function query(sql, params = []) {
    const dbPool = await createPool();
    
    logger.info(`执行SQL查询: ${sql.substring(0, 100)}${sql.length > 100 ? '...' : ''}`);
    
    const [rows] = await dbPool.query(sql, params);
    
    logger.info(`查询完成，返回 ${Array.isArray(rows) ? rows.length : 1} 条结果`);
    return rows;
}

// 获取连接
async function getConnection() {
    const dbPool = await createPool();
    return await dbPool.getConnection();
}

// 检查并创建需要的表
async function createTables() {
    const dbPool = await createPool();
    const connection = await dbPool.getConnection();
    
    try {
        // 检查并创建stats_cache表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS stats_cache (
                id INT AUTO_INCREMENT PRIMARY KEY,
                symbol VARCHAR(20) NOT NULL,
                \`interval\` VARCHAR(10) NOT NULL,
                \`range\` VARCHAR(20) NOT NULL,
                total INT NOT NULL DEFAULT 0,
                up INT NOT NULL DEFAULT 0,
                down INT NOT NULL DEFAULT 0,
                upPercent FLOAT NOT NULL DEFAULT 0,
                downPercent FLOAT NOT NULL DEFAULT 0,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_symbol_interval_range (symbol, \`interval\`, \`range\`),
                UNIQUE KEY uk_symbol_interval_range (symbol, \`interval\`, \`range\`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
        `);
        
        logger.info('数据库表检查/创建完成');
    } finally {
        connection.release();
    }
}

// 初始化方法
async function init() {
    // 创建连接池并测试连接
    const dbPool = await createPool();
    const connection = await dbPool.getConnection();
    await connection.ping();
    connection.release();
    
    // 创建表
    await createTables();
    
    logger.info('MySQL初始化完成');
    return true;
}

// 导出模块
module.exports = {
    query,
    getConnection,
    createPool,
    pool,
    init,
    createTables
}; 